namespace PMS.BLL
{
    using PMS.DBUtility;
    using PMS.Model;
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.SqlClient;
    using System.Text;

    public class OrganBLL
    {
        public int Add(Organ model)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("insert into Organ(");
            builder.Append("OrganLevelID,OrganTypeID,OrganBuildID,OrganDescription,OrganBranchCode,PermitNo,Functions,BankName,BankID,LegalPerson,OrganName,PermitOrganCount,RealityOrganCount,Address,Postcode,Contact,Tel,Remark,AddDate,AddUser,UpdateDate,OrganName2,UpdateUser,PayWay,IsOffice,LevelNo,ParentID,OrganCode,OrganClassID,OrganRoleID");
            builder.Append(") values (");
            builder.Append("@OrganLevelID,@OrganTypeID,@OrganBuildID,@OrganDescription,@OrganBranchCode,@PermitNo,@Functions,@BankName,@BankID,@LegalPerson,@OrganName,@PermitOrganCount,@RealityOrganCount,@Address,@Postcode,@Contact,@Tel,@Remark,@AddDate,@AddUser,@UpdateDate,@OrganName2,@UpdateUser,@PayWay,@IsOffice,@LevelNo,@ParentID,@OrganCode,@OrganClassID,@OrganRoleID");
            builder.Append(") ");
            builder.Append(";select @@IDENTITY");
            SqlParameter[] cmdParms = new SqlParameter[] { 
                new SqlParameter("@OrganLevelID", model.OrganLevelID), new SqlParameter("@OrganTypeID", model.OrganTypeID), new SqlParameter("@OrganBuildID", model.OrganBuildID), new SqlParameter("@OrganDescription", model.OrganDescription), new SqlParameter("@OrganBranchCode", model.OrganBranchCode), new SqlParameter("@PermitNo", model.PermitNo), new SqlParameter("@Functions", model.Functions), new SqlParameter("@BankName", model.BankName), new SqlParameter("@BankID", model.BankID), new SqlParameter("@LegalPerson", model.LegalPerson), new SqlParameter("@OrganName", model.OrganName), new SqlParameter("@PermitOrganCount", model.PermitOrganCount), new SqlParameter("@RealityOrganCount", model.RealityOrganCount), new SqlParameter("@Address", model.Address), new SqlParameter("@Postcode", model.Postcode), new SqlParameter("@Contact", model.Contact), 
                new SqlParameter("@Tel", model.Tel), new SqlParameter("@Remark", model.Remark), new SqlParameter("@AddDate", model.AddDate), new SqlParameter("@AddUser", model.AddUser), new SqlParameter("@UpdateDate", model.UpdateDate), new SqlParameter("@OrganName2", model.OrganName2), new SqlParameter("@UpdateUser", model.UpdateUser), new SqlParameter("@PayWay", model.PayWay), new SqlParameter("@IsOffice", model.IsOffice), new SqlParameter("@LevelNo", model.LevelNo), new SqlParameter("@ParentID", model.ParentID), new SqlParameter("@OrganCode", model.OrganCode), new SqlParameter("@OrganClassID", model.OrganClassID), new SqlParameter("@OrganRoleID", model.OrganRoleID)
             };
            object single = DBHelper.GetSingle(builder.ToString(), cmdParms);
            if (single == null)
            {
                return 0;
            }
            return Convert.ToInt32(single);
        }

        public bool Delete(int OrganID)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("delete from Organ ");
            builder.Append(" where OrganID=@OrganID");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@OrganID", SqlDbType.Int, 4) };
            cmdParms[0].Value = OrganID;
            return (DBHelper.ExecuteSql(builder.ToString(), cmdParms) > 0);
        }

        public List<Organ> GetList()
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select * ");
            builder.Append(" FROM view_Organ ");
            DataTable table = DBHelper.Query(builder.ToString()).Tables[0];
            Organ item = null;
            List<Organ> list = null;
            if (table.Rows.Count > 0)
            {
                list = new List<Organ>();
                foreach (DataRow row in table.Rows)
                {
                    item = new Organ();
                    if (row["OrganID"].ToString() != "")
                    {
                        item.OrganID = int.Parse(row["OrganID"].ToString());
                    }
                    if (row["OrganLevelID"].ToString() != "")
                    {
                        item.OrganLevelID = new int?(int.Parse(row["OrganLevelID"].ToString()));
                    }
                    if (row["OrganTypeID"].ToString() != "")
                    {
                        item.OrganTypeID = new int?(int.Parse(row["OrganTypeID"].ToString()));
                    }
                    if (row["OrganBuildID"].ToString() != "")
                    {
                        item.OrganBuildID = new int?(int.Parse(row["OrganBuildID"].ToString()));
                    }
                    item.OrganDescription = row["OrganDescription"].ToString();
                    item.OrganBranchCode = row["OrganBranchCode"].ToString();
                    item.PermitNo = row["PermitNo"].ToString();
                    item.Functions = row["Functions"].ToString();
                    if (row["PermitDate"].ToString() != "")
                    {
                        item.PermitDate = new DateTime?(DateTime.Parse(row["PermitDate"].ToString()));
                    }
                    item.BankName = row["BankName"].ToString();
                    item.BankID = row["BankID"].ToString();
                    item.OrganName = row["OrganName"].ToString();
                    item.LegalPerson = row["LegalPerson"].ToString();
                    if (row["PermitOrganCount"].ToString() != "")
                    {
                        item.PermitOrganCount = new int?(int.Parse(row["PermitOrganCount"].ToString()));
                    }
                    if (row["RealityOrganCount"].ToString() != "")
                    {
                        item.RealityOrganCount = new int?(int.Parse(row["RealityOrganCount"].ToString()));
                    }
                    item.Address = row["Address"].ToString();
                    item.Postcode = row["Postcode"].ToString();
                    item.Contact = row["Contact"].ToString();
                    item.Tel = row["Tel"].ToString();
                    item.Remark = row["Remark"].ToString();
                    if (row["AddDate"].ToString() != "")
                    {
                        item.AddDate = new DateTime?(DateTime.Parse(row["AddDate"].ToString()));
                    }
                    item.AddUser = row["AddUser"].ToString();
                    item.OrganName2 = row["OrganName2"].ToString();
                    item.BuildName = row["BuildName"].ToString();
                    item.LevelName = row["LevelName"].ToString();
                    item.RoleName = row["RoleName"].ToString();
                    item.TypeName = row["TypeName"].ToString();
                    item.ParentName = row["ParentName"].ToString();
                    item.PayWay = row["PayWay"].ToString();
                    if (row["IsOffice"].ToString() != "")
                    {
                        item.IsOffice = int.Parse(row["IsOffice"].ToString());
                    }
                    if (row["LevelNo"].ToString() != "")
                    {
                        item.LevelNo = new int?(int.Parse(row["LevelNo"].ToString()));
                    }
                    if (row["ParentID"].ToString() != "")
                    {
                        item.ParentID = new int?(int.Parse(row["ParentID"].ToString()));
                    }
                    item.OrganCode = row["OrganCode"].ToString();
                    if (row["OrganClassID"].ToString() != "")
                    {
                        item.OrganClassID = new int?(int.Parse(row["OrganClassID"].ToString()));
                    }
                    if (row["OrganRoleID"].ToString() != "")
                    {
                        item.OrganRoleID = new int?(int.Parse(row["OrganRoleID"].ToString()));
                    }
                    list.Add(item);
                }
            }
            return list;
        }

        public Organ GetModel(int OrganID)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("  SELECT * FROM dbo.view_Organ ");
            builder.Append("  where OrganID=@OrganID");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@OrganID", SqlDbType.Int, 4) };
            cmdParms[0].Value = OrganID;
            Organ organ = new Organ();
            DataSet set = DBHelper.Query(builder.ToString(), cmdParms);
            if (set.Tables[0].Rows.Count > 0)
            {
                DataRow row = set.Tables[0].Rows[0];
                if (row["OrganID"].ToString() != "")
                {
                    organ.OrganID = int.Parse(row["OrganID"].ToString());
                }
                if (row["OrganLevelID"].ToString() != "")
                {
                    organ.OrganLevelID = new int?(int.Parse(row["OrganLevelID"].ToString()));
                }
                if (row["OrganTypeID"].ToString() != "")
                {
                    organ.OrganTypeID = new int?(int.Parse(row["OrganTypeID"].ToString()));
                }
                if (row["OrganBuildID"].ToString() != "")
                {
                    organ.OrganBuildID = new int?(int.Parse(row["OrganBuildID"].ToString()));
                }
                organ.OrganDescription = row["OrganDescription"].ToString();
                organ.OrganBranchCode = row["OrganBranchCode"].ToString();
                organ.PermitNo = row["PermitNo"].ToString();
                organ.Functions = row["Functions"].ToString();
                if (row["PermitDate"].ToString() != "")
                {
                    organ.PermitDate = new DateTime?(DateTime.Parse(row["PermitDate"].ToString()));
                }
                organ.BankName = row["BankName"].ToString();
                organ.BankID = row["BankID"].ToString();
                organ.OrganName = row["OrganName"].ToString();
                organ.LegalPerson = row["LegalPerson"].ToString();
                if (row["PermitOrganCount"].ToString() != "")
                {
                    organ.PermitOrganCount = new int?(int.Parse(row["PermitOrganCount"].ToString()));
                }
                if (row["RealityOrganCount"].ToString() != "")
                {
                    organ.RealityOrganCount = new int?(int.Parse(row["RealityOrganCount"].ToString()));
                }
                organ.Address = row["Address"].ToString();
                organ.Postcode = row["Postcode"].ToString();
                organ.Contact = row["Contact"].ToString();
                organ.Tel = row["Tel"].ToString();
                organ.Remark = row["Remark"].ToString();
                if (row["AddDate"].ToString() != "")
                {
                    organ.AddDate = new DateTime?(DateTime.Parse(row["AddDate"].ToString()));
                }
                organ.AddUser = row["AddUser"].ToString();
                organ.OrganName2 = row["OrganName2"].ToString();
                if (row["UpdateDate"].ToString() != "")
                {
                    organ.UpdateDate = new DateTime?(DateTime.Parse(row["UpdateDate"].ToString()));
                }
                organ.UpdateUser = row["UpdateUser"].ToString();
                organ.BuildName = row["BuildName"].ToString();
                organ.LevelName = row["LevelName"].ToString();
                organ.RoleName = row["RoleName"].ToString();
                organ.TypeName = row["TypeName"].ToString();
                organ.ParentName = row["ParentName"].ToString();
                organ.PayWay = row["PayWay"].ToString();
                if (row["IsOffice"].ToString() != "")
                {
                    organ.IsOffice = int.Parse(row["IsOffice"].ToString());
                }
                if (row["LevelNo"].ToString() != "")
                {
                    organ.LevelNo = new int?(int.Parse(row["LevelNo"].ToString()));
                }
                if (row["ParentID"].ToString() != "")
                {
                    organ.ParentID = new int?(int.Parse(row["ParentID"].ToString()));
                }
                organ.OrganCode = row["OrganCode"].ToString();
                if (row["OrganClassID"].ToString() != "")
                {
                    organ.OrganClassID = new int?(int.Parse(row["OrganClassID"].ToString()));
                }
                if (row["OrganRoleID"].ToString() != "")
                {
                    organ.OrganRoleID = new int?(int.Parse(row["OrganRoleID"].ToString()));
                }
                return organ;
            }
            return null;
        }

        public List<Organ> GetNameList()
        {
            StringBuilder builder = new StringBuilder();
            builder.Append(" select  OrganID,OrganName,ParentID");
            builder.Append(" FROM Organ ");
            DataSet set = DBHelper.Query(builder.ToString());
            List<Organ> list = null;
            if (set.Tables[0].Rows.Count > 0)
            {
                list = new List<Organ>();
                Organ item = null;
                foreach (DataRow row in set.Tables[0].Rows)
                {
                    item = new Organ();
                    list.Add(item);
                }
            }
            return list;
        }

        public DataTable GetOrganUserCount(string organName)
        {
            SqlParameter[] parameters = new SqlParameter[] { new SqlParameter("@organName", organName) };
            return DBHelper.RunProcedure("[usp_GetOrganUserCount]", parameters).Tables[0];
        }

        public bool Update(Organ model)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("update Organ set ");
            builder.Append(" OrganLevelID = @OrganLevelID , ");
            builder.Append(" OrganTypeID = @OrganTypeID , ");
            builder.Append(" OrganBuildID = @OrganBuildID , ");
            builder.Append(" OrganDescription = @OrganDescription , ");
            builder.Append(" OrganBranchCode = @OrganBranchCode , ");
            builder.Append(" PermitNo = @PermitNo , ");
            builder.Append(" PermitDate = @PermitDate , ");
            builder.Append(" Functions = @Functions , ");
            builder.Append(" BankName = @BankName , ");
            builder.Append(" BankID = @BankID , ");
            builder.Append(" LegalPerson = @LegalPerson , ");
            builder.Append(" OrganName = @OrganName , ");
            builder.Append(" PermitOrganCount = @PermitOrganCount , ");
            builder.Append(" RealityOrganCount = @RealityOrganCount , ");
            builder.Append(" Address = @Address , ");
            builder.Append(" Postcode = @Postcode , ");
            builder.Append(" Contact = @Contact , ");
            builder.Append(" Tel = @Tel , ");
            builder.Append(" Remark = @Remark , ");
            builder.Append(" AddUser = @AddUser , ");
            builder.Append(" UpdateDate = @UpdateDate , ");
            builder.Append(" OrganName2 = @OrganName2 , ");
            builder.Append(" UpdateUser = @UpdateUser , ");
            builder.Append(" PayWay = @PayWay , ");
            builder.Append(" LevelNo = @LevelNo , ");
            builder.Append(" ParentID = @ParentID , ");
            builder.Append(" OrganCode = @OrganCode , ");
            builder.Append(" OrganClassID = @OrganClassID , ");
            builder.Append(" OrganRoleID = @OrganRoleID  ");
            builder.Append(" where OrganID=@OrganID ");
            SqlParameter[] cmdParms = new SqlParameter[] { 
                new SqlParameter("@OrganID", model.OrganID), new SqlParameter("@OrganLevelID", model.OrganLevelID), new SqlParameter("@OrganTypeID", model.OrganTypeID), new SqlParameter("@OrganBuildID", model.OrganBuildID), new SqlParameter("@OrganDescription", model.OrganDescription), new SqlParameter("@OrganBranchCode", model.OrganBranchCode), new SqlParameter("@PermitNo", model.PermitNo), new SqlParameter("@PermitDate", model.PermitDate), new SqlParameter("@Functions", model.Functions), new SqlParameter("@BankName", model.BankName), new SqlParameter("@BankID", model.BankID), new SqlParameter("@LegalPerson", model.LegalPerson), new SqlParameter("@OrganName", model.OrganName), new SqlParameter("@PermitOrganCount", model.PermitOrganCount), new SqlParameter("@RealityOrganCount", model.RealityOrganCount), new SqlParameter("@Address", model.Address), 
                new SqlParameter("@Postcode", model.Postcode), new SqlParameter("@Contact", model.Contact), new SqlParameter("@Tel", model.Tel), new SqlParameter("@Remark", model.Remark), new SqlParameter("@AddUser", model.AddUser), new SqlParameter("@UpdateDate", DateTime.Now.Date), new SqlParameter("@OrganName2", model.OrganName2), new SqlParameter("@UpdateUser", model.UpdateUser), new SqlParameter("@PayWay", model.PayWay), new SqlParameter("@LevelNo", model.LevelNo), new SqlParameter("@ParentID", model.ParentID), new SqlParameter("@OrganCode", model.OrganCode), new SqlParameter("@OrganClassID", model.OrganClassID), new SqlParameter("@OrganRoleID", model.OrganRoleID)
             };
            return (DBHelper.ExecuteSql(builder.ToString(), cmdParms) > 0);
        }

        public bool UpdateFunctions(int organID, string permitNo, string functions)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append(" update Organ set ");
            builder.Append(" PermitNo = @PermitNo, ");
            builder.Append(" Functions = @Functions ");
            builder.Append(" where OrganID=@OrganID ");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@OrganID", organID), new SqlParameter("@PermitNo", permitNo), new SqlParameter("@Functions", functions) };
            return (DBHelper.ExecuteSql(builder.ToString(), cmdParms) > 0);
        }
    }
}

